package com.nbgroup.utils;

import org.apache.commons.lang3.ArrayUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.Serializable;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * 多表关联动态sql工具类
 *
 * @author 王玉涛
 * @Param
 * @date 2017-10-21 9:34
 */
@Service
public class QueryUtil implements Serializable {
    private static final long serialVersionUID = 1L;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    //分页查询，多表关联
    public <T> PageUtil<T> queryByPage(String sql, Class<T> dto, Integer page, Integer pageSize) {
        if (null == page || 0 == page) {
            page = 1;
        }
        if (null == pageSize || 0 == pageSize) {
            pageSize = 10;
        }
        PageUtil pageUtil = new PageUtil();
        if (page <= 0 || pageSize <= 0) { // find all
            pageUtil.setContent(query(sql, dto));
            return pageUtil;
        } else {
            int from = (page - 1) * pageSize;
            pageSize = (pageSize > 0) ? pageSize : Integer.MAX_VALUE;
            Pattern p = Pattern.compile("order\\s*by.*");
            Matcher m = p.matcher(sql);
            String sqlpart = "";
            if (m.find()) {
                sqlpart = "(" + sql.substring(0, sql.indexOf(m.group(0))) + ") xx";
            } else {
                sqlpart = "(" + sql.substring(0, sql.length()) + ") xx";
            }
            String sqlcount = "select count(*) totalElements  from ";
            sqlcount = sqlcount + sqlpart;
            List<PageUtil> totalList = query(sqlcount.toString(), PageUtil.class);

            List<T> result = query(sql + " LIMIT ?,?", dto, ArrayUtils.addAll(new Integer[]{from, pageSize}));
//            for (T t : result) {
            System.out.println("SQL=" + sql);
            pageUtil.setTotalElements(totalList.get(0).getTotalElements());
//            }
            pageUtil.setNumber(page - 1);//设置当前页
            pageUtil.setSize(pageSize);//设置页容
            pageUtil.setNumberOfElements(result.size());//设置当前页的条数
            pageUtil.setContent(result);//将结果集给到分页工具类，进行分页
            return pageUtil;
        }
    }

    //查询,把分页进行添加
    public <T> List<T> query(String sql, Class<T> dto, Object[] params) {
        return jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<T>(dto));
    }

    /**
     * 查询，返回list
     *
     * @param sql 把条件拼装好的sql
     * @param dto 查询的dto.class
     * @return
     */
    public <T> List<T> query(String sql, Class<T> dto) {
        System.out.println(sql);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(dto));
    }


}
